package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.kit.PoiKit.Pair;
import com.jfinal.ext.kit.PoiKit.SheetData;
import com.jfinal.ext.render.excel.PoiRender;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Tower;
import util.ChristStringUtil;

import java.util.ArrayList;
import java.util.List;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Tower;


public class CheckInfoController extends BaseController {
    private static final Logger log =  Logger.getLogger(CheckInfoController.class);

	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        String name = getPara("tower.name");
		String addr_code = getPara("tower.addr_code");
		String status = getPara("tower.status");
		String owner = getPara("tower.owner");
		String operator = getPara("tower.operator");
        String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String home_county = getPara("tower.home_county");

		//高级搜索
		String addr_codes =getPara("addr_codes");
		if(ChristStringUtil.isNotEmpty(addr_codes)){
			String[] coderArr = ChristStringUtil.toArray(addr_codes, ",");
			int i=0;
			whee.append(" and addr_code in (\"");
			for (String code : coderArr) {
				i+=1;
				whee.append(code);
				if(i < coderArr.length)
					whee.append("\",\"");

			}
			whee.append("\")");
			System.out.print(whee);

		}

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }
		if(ChristStringUtil.isNotEmpty(addr_code)){
			whee.append(" and addr_code like ?");
			param.add("%" + addr_code + "%");
		}
		if(ChristStringUtil.isNotEmpty(operator)){
			whee.append(" and operator like ?");
			param.add("%" + operator + "%");
		}
		if(ChristStringUtil.isNotEmpty(status)){
			whee.append(" and status = ?");
			param.add(status);
		}
		if(ChristStringUtil.isNotEmpty(home_county)){
			whee.append(" and home_county = ?");
			param.add(home_county);
		}
		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner = ?");
			param.add(owner);
		}

		Page<Record> pager = Db.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
                "select a.id as id ,a.name as name,a.addr_code as addr_code," +
						" (case when t1.id>0 then 1 else 0 end) as id1,"+
						" (case when t2.id>0 then 1 else 0 end) as id2,"+
						" (case when t3.id>0 then 1 else 0 end) as id3,"+
						" (case when t4.id>0 then 1 else 0 end) as id4,"+
						" (case when t5.id>0 then 1 else 0 end) as id5,"+
						" (case when t6.id>0 then 1 else 0 end) as id6,"+
						" (case when t7.id>0 then 1 else 0 end) as id7,"+
						" (case when t8.id>0 then 1 else 0 end) as id8,"+
						" (case when t9.id>0 then 1 else 0 end) as id9,"+
						" (case when t10.id>0 then 1 else 0 end) as id10,"+
						" (case when t11.id>0 then 1 else 0 end) as id11,"+
						" (case when t12.id>0 then 1 else 0 end) as id12,"+
						" (case when t13.id>0 then 1 else 0 end) as id13,"+
						" (case when t14.id>0 then 1 else 0 end) as id14"
				,
                " from jms_tower a left join tower_info_1 t1 on a.addr_code=t1.c " +
						" left join tower_info_2 t2 on a.addr_code=t2.c " +
						" left join tower_info_3 t3 on a.addr_code=t3.b " +
						" left join tower_info_4 t4 on a.addr_code=t4.c " +
						" left join tower_info_5 t5 on a.addr_code=t5.b " +
						" left join tower_info_6 t6 on a.addr_code=t6.b " +
						" left join tower_info_7 t7 on a.addr_code=t7.b " +
						" left join tower_info_8 t8 on a.addr_code=t8.b " +
						" left join tower_info_9 t9 on a.addr_code=t9.b " +
						" left join tower_info_10 t10 on a.addr_code=t10.b " +
						" left join tower_info_11 t11 on a.addr_code=t11.b " +
						" left join tower_info_12 t12 on a.addr_code=t12.b " +
						" left join tower_info_13 t13 on a.addr_code=t13.b " +
						" left join tower_info_14 t14 on a.addr_code=t14.b " +
				" where entry='是' "+ whee.toString()+"order by a.addr_code desc",
                param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("addr_code",addr_code);
		setAttr("operator",operator);
		setAttr("owner",owner);
		setAttr("status",status);
		setAttr("beginDate",beginDate);
		setAttr("endDate",endDate);
		setAttr("home_county",home_county);
		render("../checkinfo_list.html");
	}
	public void add() {
		render("../tower_input.html");
	}


	public void save() {
		Tower s = getModel(Tower.class, "tower");
		s.save();
		renderDWZSuccessJson("站址信息添加成功！");
	}
	
	public void edit() {
		setAttr("tower",Tower.dao.findById(getPara("id")));
		render("../tower_input.html");
	}
	
	public void update() {
		Tower s = getModel(Tower.class, "tower");
		s.update();
		renderDWZSuccessJson("修改成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void delete() {

		String[] ids = getParaValues("ids");
		for(String id:ids){
			Tower.dao.deleteById(id);
		}
		renderDWZSuccessJson("删除成功!");

	}

	public void export(){

		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		String name = getPara("tower.name");
		String addr_code = getPara("tower.addr_code");
		String status = getPara("tower.status");
		String owner = getPara("tower.owner");
		String operator = getPara("tower.operator");
		String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String home_county = getPara("tower.home_county");

		//高级搜索
		String addr_codes =getPara("addr_codes");
		if(ChristStringUtil.isNotEmpty(addr_codes)){
			String[] coderArr = ChristStringUtil.toArray(addr_codes, ",");
			int i=0;
			whee.append(" and addr_code in (\"");
			for (String code : coderArr) {
				i+=1;
				whee.append(code);
				if(i < coderArr.length)
					whee.append("\",\"");

			}
			whee.append("\")");
			System.out.print(whee);

		}

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(addr_code)){
			whee.append(" and addr_code like ?");
			param.add("%" + addr_code);
		}
		if(ChristStringUtil.isNotEmpty(operator)){
			whee.append(" and operator like ?");
			param.add("%" + operator + "%");
		}
		if(ChristStringUtil.isNotEmpty(status)){
			whee.append(" and status = ?");
			param.add(status);
		}
		if(ChristStringUtil.isNotEmpty(home_county)){
			whee.append(" and home_county = ?");
			param.add(home_county);
		}
		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner = ?");
			param.add(owner);
		}
		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(modifyDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(modifyDate) <= TO_DAYS(?)");
			param.add(endDate);
		}

		List<Record> list = Db.find("show full columns from jms_tower");
		List<Record> towerList =Db.find("select * from " + TABLE_Tower +
				" where 1=1 " + whee.toString() + "order by modifyDate desc",param.toArray());
		List<Pair> titles = new ArrayList<Pair>();
		for(Record r : list) {
			String value = r.get("Comment");
			String key = r.get("Field");
			titles.add(new Pair(key, value));
		}

		String sheetName ="tower";
		SheetData sheetData = new SheetData(titles,towerList,sheetName);
		PoiRender excel = PoiRender.me(sheetData);
		render(excel.fileName("towers.xls"));


	}



}
